﻿/*
DECLARE	@vRemovedJobStagesTally SMALLINT;
EXEC	[RestartFramework].[RemoveETLJob]
		@pETLJobName			=	'DemoETLJob'
,		@pRemovedJobStagesTally	=	@vRemovedJobStagesTally	OUT
SELECT	@vRemovedJobStagesTally 
;
*/
SET NOCOUNT ON;
DECLARE	@vETLJobName		SYSNAME		=	'DemoETLJob'			;
DECLARE	@vETLJobId			INT										;
DECLARE	@vETLJobStageName1	SYSNAME		=	'DimProduct'			;
DECLARE	@vETLJobStageId		INT										;
DECLARE	@vETLJobStageName2	SYSNAME		=	'DimCustomer'			;
DECLARE	@vETLJobStageName3	SYSNAME		=	'DimOwner'				;
DECLARE	@vETLJobStageName4	SYSNAME		=	'DimPromotion'			;
DECLARE	@vETLJobStageName5	SYSNAME		=	'DimProductReview'		;
DECLARE	@vETLJobStageName6	SYSNAME		=	'DimProductCategory'	;
DECLARE	@vETLJobStageName7	SYSNAME		=	'DimDepartment'			;
DECLARE	@vETLJobStageName8	SYSNAME		=	'DimDocument'			;
DECLARE	@vETLJobStageName9	SYSNAME		=	'DimProductSubCategory'	;
DECLARE	@vETLJobStageName10	SYSNAME		=	'DimProductModel'		;
DECLARE	@vETLJobStageName11	SYSNAME		=	'FactInternetSales'		;
DECLARE	@vETLJobStageOrder1	SMALLINT	=	1						;
DECLARE	@vETLJobStageOrder2	SMALLINT	=	2						;
EXEC	[RestartFramework].[PutETLJob]
		@pETLJobName	=	@vETLJobName
,		@pETLJobId		=	@vETLJobId		OUTPUT;
SELECT	[ETLJobId]		=	@vETLJobId
,		[ETLJobName]	=	@vETLJobName;


EXEC	[RestartFramework].[PutETLJobStage]
		@pETLJobID				=	@vETLJobID
,		@pETLJobStageName		=	@vETLJobStageName1
,		@pETLJobStageOrder		=	@vETLJobStageOrder1
,		@pETLJobStageEnabled	=	1
,		@pETLJobStageId			=	@vETLJobStageId			OUTPUT
;
EXEC	[RestartFramework].[PutETLJobStage]
		@pETLJobID				=	@vETLJobID
,		@pETLJobStageName		=	@vETLJobStageName2
,		@pETLJobStageOrder		=	@vETLJobStageOrder1
,		@pETLJobStageEnabled	=	1
,		@pETLJobStageId			=	@vETLJobStageId			OUTPUT
;
EXEC	[RestartFramework].[PutETLJobStage]
		@pETLJobID				=	@vETLJobID
,		@pETLJobStageName		=	@vETLJobStageName3
,		@pETLJobStageOrder		=	@vETLJobStageOrder1
,		@pETLJobStageEnabled	=	1
,		@pETLJobStageId			=	@vETLJobStageId			OUTPUT
;
EXEC	[RestartFramework].[PutETLJobStage]
		@pETLJobID				=	@vETLJobID
,		@pETLJobStageName		=	@vETLJobStageName4
,		@pETLJobStageOrder		=	@vETLJobStageOrder1
,		@pETLJobStageEnabled	=	1
,		@pETLJobStageId			=	@vETLJobStageId			OUTPUT
;
EXEC	[RestartFramework].[PutETLJobStage]
		@pETLJobID				=	@vETLJobID
,		@pETLJobStageName		=	@vETLJobStageName5
,		@pETLJobStageOrder		=	@vETLJobStageOrder1
,		@pETLJobStageEnabled	=	1
,		@pETLJobStageId			=	@vETLJobStageId			OUTPUT
;
EXEC	[RestartFramework].[PutETLJobStage]
		@pETLJobID				=	@vETLJobID
,		@pETLJobStageName		=	@vETLJobStageName6
,		@pETLJobStageOrder		=	@vETLJobStageOrder1
,		@pETLJobStageEnabled	=	1
,		@pETLJobStageId			=	@vETLJobStageId			OUTPUT
;
EXEC	[RestartFramework].[PutETLJobStage]
		@pETLJobID				=	@vETLJobID
,		@pETLJobStageName		=	@vETLJobStageName7
,		@pETLJobStageOrder		=	@vETLJobStageOrder1
,		@pETLJobStageEnabled	=	1
,		@pETLJobStageId			=	@vETLJobStageId			OUTPUT
;
EXEC	[RestartFramework].[PutETLJobStage]
		@pETLJobID				=	@vETLJobID
,		@pETLJobStageName		=	@vETLJobStageName8
,		@pETLJobStageOrder		=	@vETLJobStageOrder1
,		@pETLJobStageEnabled	=	1
,		@pETLJobStageId			=	@vETLJobStageId			OUTPUT
;
EXEC	[RestartFramework].[PutETLJobStage]
		@pETLJobID				=	@vETLJobID
,		@pETLJobStageName		=	@vETLJobStageName9
,		@pETLJobStageOrder		=	@vETLJobStageOrder1
,		@pETLJobStageEnabled	=	1
,		@pETLJobStageId			=	@vETLJobStageId			OUTPUT
;
EXEC	[RestartFramework].[PutETLJobStage]
		@pETLJobID				=	@vETLJobID
,		@pETLJobStageName		=	@vETLJobStageName10
,		@pETLJobStageOrder		=	@vETLJobStageOrder1
,		@pETLJobStageEnabled	=	1
,		@pETLJobStageId			=	@vETLJobStageId			OUTPUT
;
EXEC	[RestartFramework].[PutETLJobStage]
		@pETLJobID				=	@vETLJobID
,		@pETLJobStageName		=	@vETLJobStageName11
,		@pETLJobStageOrder		=	@vETLJobStageOrder2
,		@pETLJobStageEnabled	=	1
,		@pETLJobStageId			=	@vETLJobStageId			OUTPUT
;

SELECT	* 
FROM	[RestartFramework].[GetDistinctETLJobStageOrder]('DemoETLJob')
SELECT	* 
FROM	[RestartFramework].[GetETLJobStagesPerETLJobNameETLJobStageOrder]('DemoETLJob',@vETLJobStageOrder1,DEFAULT);
SELECT	* 
FROM	[RestartFramework].[GetETLJobStagesPerETLJobNameETLJobStageOrder]('DemoETLJob',@vETLJobStageOrder2,DEFAULT);

SELECT	*
FROM	[RestartFramework].[GetETLJobStageVisualisation](@vETLJobName)